索引及执行计划

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。对于一个SQL语句，在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

总之：索引是为了优化查询，而select 查询有三种情况:缓存查询(不在mysql中进行数据查询),全表扫描,索引扫描

索引的种类

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

BTREE（btree  b+tree b*tree）

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

Btree索引分类

聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引.
辅助索引:人为创建的(普通索引,覆盖索引)
唯一索引:人为创建(普通索引,聚集索引)

执行计划

见https://www.cnblogs.com/jiangzimin/p/10274360.html 

运维规范

一、数据库索引的设计原则：

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?

0.建表时一定要有主键,如果不相关列可以作为主键,做一个无关列

1.选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3.为常作为where查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。
3.1 经常查询
3.2 列值的重复值少(业务层面调整)

注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

------------------------以上的是重点关注的，以下是能保证则保证的--------------------

5.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6.删除不再使用或者很少使用的索引(percona toolkit)
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.大表加索引,要在业务不繁忙期间操作

总结

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
(6) 索引维护要避开业务繁忙期

不走索引的情况（开发规范）

1) 没有查询条件，或者查询条件没有建立索引

select * from tab; 全表扫描。

在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。

1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。

（1）select * from tab;

SQL改写成以下语句：
selec  * from tab  order by  price  limit 10      需要在price列上建立索引


（2）select  * from  tab where name='zhangsan'          name列没有索引

改：
    1、换成有索引的列作为查询条件
    2、将name列建立索引

2) 查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有索引

select * from tab  where id>500000;

如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

3) 索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。

4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算
函数运算
desc select * from blog_userinfo where DATE_FORMAT(last_login,'%Y-%m-%d') >= '2019-01-01';

子查询

5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 

select * from t1 where telnum=110;

6) <> ，not in 不走索引
7) like "%_" 百分号在最前面不走

8) 单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);


走索引的情况测试：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';    ----->部分走索引
不走索引的：
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE  age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex='m';

压力测试

1、模拟数据库数据
为了测试我们创建一个oldboy的库创建一个t1的表，然后导入50万行数据，脚本如下：
vim slap.sh
#!/bin/bash  
HOSTNAME="localhost" 
PORT="3306" 
USERNAME="root" 
PASSWORD="123" 
DBNAME="oldboy" 
TABLENAME="t1" 
#create database 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 
create_db_sql="create database if not exists ${DBNAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 
#create table 
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 
#insert data to table 
i="1" 
while [ $i -le 500000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 
let i++  
done  
#select data  
select_sql="select count(*) from ${TABLENAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

执行脚本：
sh slap.sh


2、检查数据可用性
mysql -uroot -p123
select count(*) from oldboy.t1;

3、在没有优化之前我们使用mysqlslap来进行压力测试
# 100个线程，总共2000个请求
mysqlslap --defaults-file=/etc/my.cnf \
 --concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose